package com.yc.book.util;

import java.sql.*;
import java.util.*;

public class DBHelper {

    private static String
            url = "jdbc:mysql:///test",
            driver = "com.mysql.jdbc.Driver",
            username = "root",
            password = "a";

    static{
        try {
            /**
             * 从配置文件加载数据库 4 个连接参数 （ 略 ）
             */
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("加载驱动失败！",e);
        }
    }

    /**
     * 创建连接
     */
    public static Connection createConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            throw new RuntimeException("获取连接失败！", e);
        }
    }

    /**
     * 创建与编译语句对象
     * @param keyColumn： 主键列，必须是由序列生成的值， 才能使用该参数
     * @param params： 参数数组
     * 参考例子： prepareStatement(conn,"insert into dept values(seq_dept.nextval,?,?)"
    , "deptno","测试部门","火星");
     */
    private static PreparedStatement prepareStatement(
            Connection connection, String sql, String keyColumn, Object... params)
            throws SQLException {
        System.out.println("SQL: " + sql);
        System.out.println("参数: " + Arrays.toString(params));
        PreparedStatement preparedStatement = keyColumn == null ?
                connection.prepareStatement(sql) :
                connection.prepareStatement(sql, new String[]{keyColumn});
        for (int i = 0; i < params.length; i++) {
            preparedStatement.setObject(i + 1, params[i]);
        }
        return preparedStatement;
    }

    /**
     * Sql语句执行器
     */
    public static interface SqlExecutor < T >{
        T execute(Connection connection) throws Exception;
    }

    /**
     * 以事务管理方式执行语句块 (模板模式)
     * @param sqlExecutor   Sql语句执行器
     */
    public static < T > T execute(SqlExecutor sqlExecutor) {
        Connection connection = createConnection();
        try {
            connection.setAutoCommit(false);
            T ret = (T) sqlExecutor.execute(connection);
            connection.commit();
            return ret;
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (Exception e1) {
                throw new RuntimeException("回滚失败！", e1);
            }
            throw new RuntimeException("SQL执行失败！", e);
        } finally {
            try {
                connection.close();
            } catch (Exception e1) {
                throw new RuntimeException("关闭连接失败！", e1);
            }
        }

    }

    /**
     * 执行查询语句
     */
    public static List< Map< String, Object > > selectListMap(String sql, Object... params) {
        return execute(conn -> {
            PreparedStatement ps = prepareStatement(conn, sql, null, params);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            List<Object> rows = new ArrayList<>();
            while (rs.next()) {
                Map row = new LinkedHashMap<>();
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnName(i + 1).toLowerCase();
                    Object columnValue = rs.getObject(i + 1);
                    row.put(columnName, columnValue);
                }
                rows.add(row);
            }
            return rows;
        });
    }

    /**
     * 执行增删改语句
     */
    public static int update(String sql, Object... params) {
        return execute(conn->prepareStatement(createConnection(), sql, null, params).executeUpdate());
    }

    /**
     * 执行新增语句，新增字段中有带序列生成主键值，该方法返回第一行的主键值
     * @param keyColumn： 主键列，必须是由序列生成的值， 才能使用该参数
     * @param params： 参数数组
     */
    public static int insert(String sql, String keyColumn, Object... params) {
        return execute(conn->{
            PreparedStatement ps = prepareStatement(createConnection(), sql, keyColumn, params);
            ps.executeUpdate();
            ResultSet generatedKeys = ps.getGeneratedKeys();
            generatedKeys.next();
            return generatedKeys.getInt(1);
        });
    }
}
